package org.example.inventory.dao.impl;



import org.example.inventory.dao.PurchaseDao;
import org.example.inventory.entity.Purchase;
import org.example.inventory.exception.JdbcException;
import org.example.inventory.util.JdbcUtil;
import org.example.inventory.util.ResultSetHandler;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.*;

public class PurchaseDaoImpl implements PurchaseDao {

    private static ResultSetHandler<Purchase> handler= rs -> {
        try {
            return new Purchase(
                    rs.getInt("id"),
                    rs.getInt("product_id"),
                    rs.getInt("quantity"),
                    rs.getBigDecimal("purchase_price"),
                    rs.getInt("operator_id"),
                    rs.getTimestamp("purchase_time").toLocalDateTime().toLocalDate()
            );
        }catch (SQLException e) {
            throw new JdbcException("结果集映射失败",e);
        }
    };

    @Override
    public Purchase insertPurchase(Purchase purchase) {
        String sql="insert into purchase (product_id,quantity,purchase_price,operator_id) values(?,?,?,?)";
        JdbcUtil.executeUpdate(sql,purchase.getProductId(),purchase.getQuantity(),purchase.getPurchasePrice(),purchase.getOperatorID());
        String getLastIdSql="select LAST_INSERT_ID()";
        Integer id=JdbcUtil.executeQuerySingle(getLastIdSql,rs -> rs.getInt(1));
        //填充id并返回
        return selectPurchaseById(id);

    }

    @Override
    public Purchase selectPurchaseById(Integer id) {
        String result="select * from purchase where id=?";
        return JdbcUtil.executeQuerySingle(result,handler,id);
    }

    @Override
    public int updatePurchase(Purchase purchase) {
        //id都为空直接抛出异常
        if(Objects.isNull(purchase.getId())){
            //抛出自定义的运行时异常
            throw new JdbcException("id is null");
        }
        //1=1没有意义，为了防止没有set语法出错
        StringBuilder sql=new StringBuilder("update purchase set id=id,");
        //不确定有几个？，创建动态数组，存储sql参数
        List<Object> params=new ArrayList<>();
        //拼接字符串首行保留空格，结尾保留逗号
        if(Objects.nonNull(purchase.getProductId())){
            sql.append(" product_id=?,");
            params.add(purchase.getProductId());
        }
        if(Objects.nonNull(purchase.getQuantity())){
            sql.append(" quantity=?,");
            params.add(purchase.getQuantity());
        }
        if(Objects.nonNull(purchase.getPurchasePrice())){
            sql.append(" purchase_price=?,");
            params.add(purchase.getPurchasePrice());
        }
        if(Objects.nonNull(purchase.getOperatorID())){
            sql.append(" operator_id=?,");
            params.add(purchase.getOperatorID());
        }
        if(Objects.nonNull(purchase.getPurchaseDate())){
            sql.append(" purchase_time=?,");
            params.add(purchase.getPurchaseDate());
        }

        //去除最后一个逗号
//        sql= new StringBuilder(sql.substring(0, sql.length() - 1));
        //确保多余的，在最后一个符号上
        sql.deleteCharAt(sql.length()-1);
        //拼接where条件
        sql.append(" where id=?");
        params.add(purchase.getId());
        return JdbcUtil.executeUpdate(sql.toString(), params.toArray());
    }

    @Override
    public int deletePurchase(Integer id) {
        String sql="delete from purchase where id = ?";
        return JdbcUtil.executeUpdate(sql,id);
    }

    @Override
    public List<Purchase> selectPurchasesByPage(int pageNum, int pageSize) {
        //参数校验
        if(pageNum<1){
            pageNum=1;
        }
        if(pageSize<1){
            pageSize=10;
        }
        //第一个问号是pagesize每页数量 往后查几行 第二个问号是offset偏移量 第几行开始查
        String sql="select * from purchase limit ? offset ?";
        int offset=(pageNum-1)*pageSize;
        return JdbcUtil.executeQueryList(sql,handler,pageSize,offset);
    }

    @Override
    public List<Purchase> searchPurchases(Integer productId, Integer operatorId, Integer minQuantity) {
        //1.基础SQL
        StringBuilder sql = new StringBuilder("SELECT * FROM purchase WHERE 1=1");
        //用动态数组存放动态参数
        List<Object> params = new ArrayList<>();
        if(Objects.nonNull(productId)){
            sql.append(" and product_id=?");
            params.add(productId);
        }
        if(Objects.nonNull(operatorId)){
            sql.append(" and operator_id=?");
            params.add(operatorId);
        }
        if (minQuantity != null && minQuantity > 0) {
            sql.append(" AND stock >= ?");
            params.add(minQuantity);
        }
        //3.执行查询，映射查询结果
        return JdbcUtil.executeQueryList(sql.toString(), handler, params.toArray());

    }

    @Override
    public int sumQuantityByProductId(Integer productId) {
        String sql="select sum(quantity) from purchase where product_id=?";
        return JdbcUtil.executeQuerySingle(sql,rs->rs.getInt(1),productId);
    }

    @Override
    public Map<Integer, BigDecimal> sumAmountBySupplier() {
        String sql="select supplier_id from purchase";
        List<Integer> sp = JdbcUtil.executeQueryList(sql, rs -> rs.getInt(1));
        String sql1="select sum(quantity) from purchase where supplier_id=?";
        Map<Integer,BigDecimal> map = new HashMap<>();
        for (Integer i : sp) {
            map.put(i,JdbcUtil.executeQuerySingle(sql1,rs -> rs.getBigDecimal(1),i));
        }
        return map;
    }

    @Override
    public BigDecimal getLatestPurchasePrice(Integer productId) {
        String sql="SELECT purchase_price FROM purchase WHERE product_id = ? ORDER BY purchase_time DESC LIMIT 1";

        return JdbcUtil.executeQuerySingle(sql,rs->rs.getBigDecimal(1),productId);
    }
}
